package www.lagou.app;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;
import www.lagou.entity.Dept;
import www.lagou.entity.Employee;
import www.lagou.utils.DruidUtils;

import java.sql.SQLException;
import java.util.List;

/**
 * @author wanggege
 * 编程题3
 */
public class Demo03Test {

    /**
     * @throws SQLException 需求一：查询所有的员工信息 (不包含没有部门的员工)
     */
    @Test
    public void queryTest01() throws SQLException {

        //使用连接池创建QueryRunner对象 自动模式
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        //编写查询SQL
        String sql = "select * from employee where did is not null; ";

        List<Employee> employeeList = qr.query(sql, new BeanListHandler<Employee>(Employee.class));

        for (Employee employee : employeeList) {
            int did = employee.getDid();
            Dept dept = queryDeptById(did);

            System.out.println(employee + "{" + dept.getDeptname() + "}");
        }


    }

    /**
     * @throws SQLException 需求二：查询每个员工的 姓名, 薪资 和 所属部门名称
     */
    @Test
    public void queryTest02() throws SQLException {

        //使用连接池创建QueryRunner对象 自动模式
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        //编写查询有部门信息的员工SQL
        String sql = "select * from employee ";


        List<Employee> employeeList = qr.query(sql, new BeanListHandler<Employee>(Employee.class));

        for (Employee employee : employeeList) {
            //获取员工的姓名，薪资和部门名称
            String name = employee.getName();
            double salary = employee.getSalary();
            int did = employee.getDid();
            if (did == 0) {
                System.out.println("[姓名：" + name + "\t\t薪资：" + salary + "\t部门名称：无部门]");
            } else {
                Dept dept = queryDeptById(did);
                String deptname = dept.getDeptname();
                //打印员工信息
                System.out.println("[姓名：" + name + "\t\t薪资：" + salary + "\t部门名称：" + deptname + "]");
            }
        }


    }

    //查询部门名称并返回
    public Dept queryDeptById(int id) throws SQLException {

        //使用连接池创建QueryRunner对象 自动模式
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        String sql = "select deptname from dept where id = ?";

        Dept dept = qr.query(sql, new BeanHandler<Dept>(Dept.class), id);

        return dept;

    }
}
